import os
import xlwings as xw
import pandas as pd

file_path = '../Files'
file_list = os.listdir(file_path)
app = xw.App(visible=True, add_book=False)
for file in file_list:
    if file.startswith('~$'):
        continue
    file_with_folder = os.path.join(file_path, file)
    workbook = app.books.open(file_with_folder)
    worksheet = workbook.sheets['规格表']
    if worksheet is not None:
        values = worksheet.range('A1').options(pd.DataFrame, header=1, index=False, expand='table').value  # 读取指定工作表数据
        new_values = values["规格"].str.split('*', expand=True)  # 根据*号拆分 “规格” 列
        values['Length'] = new_values[0]
        values['Width'] = new_values[1]
        values['High'] = new_values[2]
        split_values = values.drop(columns=['规格'], inplace=False)
        worksheet_new = workbook.sheets['规格表2']
        if worksheet_new is None:
            worksheet_new = workbook.sheets.add('规格表2')
        worksheet_new.clear()
        worksheet_new['A1'].options(index=False).value = split_values
        worksheet_new.autofit()
    workbook.save()
    workbook.close()
app.quit()
